﻿using Dapper;
using Direct.IDAL;
using DirectSell.Model.Money;
using DirectSell.Model.Query;
using DirectSell.Model.User;
using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Webdiyer.WebControls.Mvc;

namespace DirectSell.DAL
{
    /// <summary>
    /// 用户数据访问基类
    /// </summary>
    public abstract class AbsRepository:IAbsRepository
    {
        #region 初始化信息--郭文昊--2018年8月9日23:01:10
        /// <summary>
        /// 表名
        /// </summary>
        protected  string TableName { get; set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        public AbsRepository(string tableName)
        {
            this.TableName = tableName;
        }
        public AbsRepository() { }
        #endregion

        #region Connection--郭文昊--2018年8月9日22:29:35
        /// <summary>
        /// connectionString
        /// </summary>
        public static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"] as string;
        /// <summary>
        /// Connection
        /// </summary>
        public IDbConnection Connection { get { return new SqlConnection(connectionString); } }
        #endregion

        #region 查询--郭文昊--2018年8月9日22:38:02
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="R"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IEnumerable<R> Query<R>(string sql)
        {
            using (var connection = Connection)
            {
                return connection.Query<R>(sql);
            }
        }
        #endregion

        #region 查询(异步)--郭文昊--2018年10月29日00:46:39
        /// <summary>
        /// 查询(异步)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<IEnumerable<R>> QueryAsync<R>(string sql)
        {
            using(var connection = Connection)
            {
                return await connection.QueryAsync<R>(sql);
            }
        }
        #endregion

        #region 查询用户数量--郭文昊--2018年10月28日18:49:43
        /// <summary>
        /// 查询用户数量 异步
        /// </summary>
        /// <typeparam name="R"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<long> QueryCount<T>(string sql)
        {
            using (var connection = Connection)
            {
               return await connection.QueryFirstAsync<long>(sql);
            }
        }
        /// <summary>
        /// 查询用户数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public long QueryFirst<T>(string sql)
        {
            using (var connection = Connection)
            {
                return  connection.QueryFirst<long>(sql);
            }
        }
        /// <summary>
        /// 查一条
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T QueryFirstT<T>(string sql)
        {
            using (var connection = Connection)
            {
                return connection.QueryFirst<T>(sql);
            }
        }
        /// <summary>
        /// 查一条异步
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<T> QueryFirstTAsync<T>(string sql)
        {
            using (var connection = Connection)
            {
                return await connection.QueryFirstAsync<T>(sql);
            }
        }

        #endregion

        #region Update方法--郭文昊--2018年8月6日11:21:58
        /// <summary>
        /// 更新方法模版
        /// </summary>
        /// <param name="setValue">更新字段和值</param>
        /// <param name="strWhere"></param>
        /// <param name="arrayList"></param>
        public void Update(string setValue, string strWhere, ref Hashtable hsTable)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("update {0} set {1}", TableName, setValue);
            strSql.Append(GetWhere(strWhere));
            hsTable.Add(strSql.ToString(), null);
        }
        #endregion

        #region Insert方法 --郭文昊--2018年8月9日23:04:25
        /// <summary>
        /// Insert方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public void Insert<T>(T model,ref Hashtable hsTable)
        {
            using (var connection = Connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("INSERT INTO {0}", TableName);
                strSql.Append(GetInsertFields());
                strSql.Append(" VALUES ");
                strSql.Append(GetInsertValue());
                hsTable.Add(strSql.ToString(),model);
            }
        }
        #endregion

        #region Insert--郭文昊--2018年8月9日23:15:42
        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public int Insert<T>(T model)
        {
            using (var connection = Connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("INSERT INTO {0}", TableName);
                strSql.Append(GetInsertFields());
                strSql.Append(" VALUES ");
                strSql.Append(GetInsertValue());
                return connection.Execute(strSql.ToString(),model);
            }
        }
        #endregion

        #region Update -- 郭文昊--2018年8月9日23:25:23
        /// <summary>
        /// Update
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="setValue"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public int Update<T>(T model, string setValue, string strWhere)
        {
            using (var connection = Connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("update {0} set {1}", TableName, setValue);
                strSql.Append(GetWhere(strWhere));
                return connection.Execute(strSql.ToString(), model);
            }
        }
        #endregion

        #region 执行一条Sql提交事务 --郭文昊--2018年8月14日21:42:57
        /// <summary>
        /// 执行一条sql提交事务
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool Execute (string strSql)
        {
            using (var connection = Connection)
            {
                connection.Open();
                IDbTransaction transaction = connection.BeginTransaction();
                
                try
                {
                   if (connection.Execute(strSql, null, transaction) < 1)
                   {
                        transaction.Rollback();
                        return false;
                    }
                    transaction.Commit();
                    return true;
                }
                catch(Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
              
            }
        }

        /// <summary>
        /// 直接执行一条sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExcSql(string sql)
        {
            using (var connection = Connection)
            {
                return connection.Execute(sql);
            }
        }
        #endregion

        #region 获取插入行--郭文昊--2018年8月9日23:08:49
        /// <summary>
        /// 获取插入行
        /// </summary>
        /// <returns></returns>
        protected virtual string GetInsertFields()
        {
            return string.Empty;
        }
        #endregion

        #region 获取插入Value--郭文昊--2018年8月9日23:10:59
        /// <summary>
        /// 获取插入Value
        /// </summary>
        /// <returns></returns>
        protected virtual string GetInsertValue()
        {
            return string.Empty;
        }
        #endregion

        #region 获取where条件--郭文昊--2018年8月9日23:01:59
        /// <summary>
        /// 获取where条件
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        protected string GetWhere(string strWhere)
        {
            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                return string.Format(" WHERE {0} ", strWhere);
            }
            return string.Empty;
        }
        #endregion

        #region 提交事务--支持乐观锁--郭文昊--2018年8月9日22:55:59
        /// <summary>
        /// 提交事务支持乐观锁
        /// </summary>
        /// <param name="hsTable">哈希表</param>
        /// <returns></returns>
        public bool CommitDapperTranLock(Hashtable hsTable)
        {
            using (var connection = Connection)
            {
                connection.Open();
                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    foreach (var item in hsTable.Keys)
                    {
                        if (connection.Execute(item as string, hsTable[item], transaction) < 1)
                        {
                            transaction.Rollback();
                            return false;
                        }
                    }
                    transaction.Commit();
                    return true;
                }

                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
            }
        }
        #endregion

        #region 分页查询--郭文昊--2018年11月14日10:14:41
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="R"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task <PagedList<R>> PageQuery<R>(PageParam model)
        {
            using(var connection = Connection)
            {
                StringBuilder str = new StringBuilder();
                str.AppendFormat(" select top (select {0}) *  ",model.PageSize);
                str.AppendFormat(" from (select row_number() over(order by {0}) as rownumber,{1} ",model.OrderBy??"Id DESC",model.Fields??"*");
                str.AppendFormat("  from {0} where {1} ) temp_row  ",model.TableName,string.IsNullOrEmpty(model.StrWhere)?"1=1":model.StrWhere);
                str.AppendFormat(" where rownumber>({0}-1)*{1}; ",model.PageIndex,model.PageSize);

                var tasklist = await  connection.QueryAsync<R>(str.ToString());

               // var list = tasklist.AsList();

                StringBuilder strCount = new StringBuilder();

                strCount.AppendFormat(" SELECT COUNT(*) FROM {0} WHERE {1} ", model.TableName, string.IsNullOrEmpty(model.StrWhere)?"1=1":model.StrWhere);

                var totalCount = await connection.QueryFirstAsync<int>(strCount.ToString());

                var data = new PagedList<R>(tasklist, model.PageIndex,model.PageSize,totalCount);
                data.CurrentPageIndex = model.PageIndex;
                return data;
            }
        }
        #endregion

        #region 执行存储过程--事务
        /// <summary>
        /// 执行存储过程 事务
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public bool ExecProc(string procName, DynamicParameters param)
        {
            using (var connection = Connection)
            {
                connection.Open();
                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    var result = connection.Execute(procName, param, transaction, null, CommandType.StoredProcedure);
                    if (result < 1)
                    {
                        transaction.Rollback();
                        return false;
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
              
            }
        }
        #endregion

        #region 写入消费表
        /// <summary>
        /// 写入消费表
        /// </summary>
        /// <param name="req"></param>
        /// <param name="msg"></param>
        /// <param name="type"></param>
        /// <param name="hstable"></param>
        public void InsertConsumption(Consumption req,  ref Hashtable hstable)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" INSERT INTO Consumption ");
            sb.Append(" ([Operator] ");
            sb.Append(" ,[Amout] ");
            sb.Append(" ,[Affair] ");
            sb.Append(" ,[CreateDate] ");
            sb.Append(" ,[Relevant] ");
            sb.Append(" ,[LogType]) ");
            sb.Append(" VALUES ");
            sb.AppendFormat(" ('{0}' ", req.Operator);
            sb.AppendFormat(" ,'{0}' ", req.Amout);
            sb.AppendFormat(" ,'{0}' ", req.Affair);
            sb.AppendFormat(" ,'{0}' ", req.CreateDate);
            sb.AppendFormat(" ,'{0}' ", req.Relevant);
            sb.AppendFormat(" ,{0} )", req.LogType);
            hstable.Add(sb.ToString(), null);
        }
        #endregion

        #region 判断二级密码
        /// <summary>
        /// 判断二级密码
        /// </summary>
        /// <param name="UserCode"></param>
        /// <param name="SecondPassword"></param>
        /// <returns></returns>
        public bool CheckSpassword(string UserCode, string SecondPassword)
        {
            var sql = new StringBuilder().Append(" SELECT COUNT(*)  FROM UserInforMations ")
               .AppendFormat(" WHERE UserCode ='{0}'  AND UserSecondPassWord='{1}'", UserCode, SecondPassword);
            int result = this.QueryFirstT<int>(sql.ToString());
            return result > 0;
        }
        #endregion

        #region 查询用户信息
        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <param name="UserCode"></param>
        /// <returns></returns>
        public UserInforMationDetails GetUserInfo(string UserCode)
        {
            var sql = new StringBuilder().Append(" SELECT *  FROM UserInforMations ")
               .AppendFormat(" WHERE UserCode ='{0}'", UserCode);
            var model = Query<UserInforMationDetails>(sql.ToString());
            return model.FirstOrDefault();
        }
        #endregion

        #region 获取参数 -- 郭文昊
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="param"></param>
        /// <returns></returns>
        protected virtual object GetParam<T>(T param)
        {
            return new { };
        }
        #endregion

    }
}